package com.wanmait.shoppingsys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.wanmait.shoppingsys.util.DbHelper;
import com.wanmait.shoppingsys.util.ManagePager;
import com.wanmait.shoppingsys.util.Pager;
import com.wanmait.shoppingsys.util.ShopCar;
import com.wanmait.shoppingsys.vo.Goods;
import com.wanmait.shoppingsys.vo.Specialty;
import com.wanmait.shoppingsys.vo.Trade;
import com.wanmait.shoppingsys.vo.UserInfo;

public class GoodsDAO {

	private static GoodsDAO goodsDAO;

	private GoodsDAO() {

	}

	public static GoodsDAO getDAO() {
		if (goodsDAO == null) {
			goodsDAO = new GoodsDAO();
		}
		return goodsDAO;
	}

	private List<Goods> findbysql(String sql, List params) {
		ArrayList<Goods> goodss = new ArrayList<Goods>();

		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; params != null && i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {

				Goods goods = new Goods();
				goods.setId(rs.getInt("id"));
				goods.setAddress(rs.getString("address"));
				goods.setIncomeName(rs.getString("icome_name"));
				goods.setOrderNum(rs.getString("order_num"));
				goods.setOrderState(rs.getString("order_state"));
				goods.setPayTime(rs.getTimestamp("pay_time"));
				goods.setPhone(rs.getString("phone"));
				goods.setTotalPrice(rs.getDouble("total_price"));
				goods.setUserId(rs.getInt("user_id"));

			}

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DbHelper.close(rs, ps, conn);
		}
		return goodss;
	}

	public List<Goods> findall() { // cha zhao suo you

		String sql = "SELECT * from goods_order";

		return this.findbysql(sql, null);
	}

	public Goods findById(Integer id) {
		Goods goods = null;
		String sql = "SELECT * from goods_order WHERE id=?"; // tong guo id
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				goods = new Goods();
				goods.setId(rs.getInt("id"));
				goods.setAddress(rs.getString("address"));
				goods.setIncomeName(rs.getString("icome_name"));
				goods.setOrderNum(rs.getString("order_num"));
				goods.setOrderState(rs.getString("order_state"));
				goods.setPayTime(rs.getTimestamp("pay_time"));
				goods.setPhone(rs.getString("phone"));
				goods.setTotalPrice(rs.getDouble("total_price"));
				goods.setUserId(rs.getInt("user_id"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.close(rs, ps, conn);
		}
		return goods;
	}
	
	
	public ArrayList<Trade> FindById(Integer id) {          //通过总订单ID查找小订单 
		ArrayList<Trade> trades = new ArrayList<Trade>();
		String sql = "SELECT * from trade_order left join goods_order on trade_order.order_id = goods_order.id left join specialty on specialty.id = trade_order.specialty_id WHERE goods_order.id = ?"; 
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				Trade trade = new Trade();
				trade.setId(rs.getInt("id"));
				trade.setOrderPrice(rs.getDouble("order_price"));
				trade.setOrderCount(rs.getInt("order_count"));
				
				Specialty specialty = new Specialty();
				specialty.setName(rs.getString("name"));
				trade.setSpecialty(specialty);
				
				trades.add(trade);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.close(rs, ps, conn);
		}
		return trades;
	}
	

	public void insert(Goods goods) {
		String sql = "INSERT INTO goods_order(total_price,order_state,phone,pay_time,user_id,address,income_name,order_num) VALUES(?,?,?,?,?,?,?,?)";
		ArrayList params = new ArrayList();
		params.add(goods.getTotalPrice());
		params.add(goods.getOrderState());
		params.add(goods.getPhone());
		params.add(goods.getPayTime());
		params.add(goods.getUserId());
		params.add(goods.getAddress());
		params.add(goods.getIncomeName());
		params.add(goods.getOrderNum());
		DbHelper.update(sql, params);
	}

	public void delete(Integer id) {
		String sql = "DELETE FROM  goods_order WHERE id=?";
		ArrayList params = new ArrayList();
		params.add(id);

		DbHelper.update(sql, params);
	}

	public void update(Goods goods) {
		String sql = "UPDATE goods_order SET total_price=?,order_state=?,phone=?,pay_time=?,user_id=?,address=?,income_name=?,order_num=? where id=?";
		ArrayList params = new ArrayList();
		params.add(goods.getTotalPrice());
		params.add(goods.getOrderState());
		params.add(goods.getPhone());
		params.add(goods.getPayTime());
		params.add(goods.getUserId());
		params.add(goods.getAddress());
		params.add(goods.getIncomeName());
		params.add(goods.getOrderNum());
		DbHelper.update(sql, params);
	}

	public Pager findListById(Pager pager) // 登录之后根据用户id查询所有买的商品
	{
		HashMap<String, Goods> goodsMap = new HashMap<String, Goods>();		
		pager.setRowCount(this.getDataCount(pager.getId()));
		
		String sql = "SELECT trade_order.*,goods_order.*, specialty.* from trade_order right join \r\n" + 
				"(select * from goods_order WHERE goods_order.user_id=? limit ?,? )as goods_order\r\n" + 
				"on goods_order.id=trade_order.order_id \r\n" +
				"left join specialty on specialty.id = trade_order.specialty_id " ;
				
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, pager.getId());
			ps.setInt(2, (pager.getPageNum() - 1) * pager.getPageSize());
			ps.setInt(3, pager.getPageSize());
			
			rs = ps.executeQuery();
			while(rs.next()){
				Trade trade = new Trade();
				trade.setSpecialtyId(rs.getInt("specialty_id"));
				trade.setOrderCount(rs.getInt("order_count"));
				trade.setOrderPrice(rs.getDouble("order_price"));

				Specialty specialty = new Specialty();
				specialty.setName(rs.getString("name"));
				specialty.setPic(rs.getString("pic"));
				trade.setSpecialty(specialty);
				
				
				Goods goods = goodsMap.get(rs.getString("order_num"));
				if(goods==null) {
					goods = new Goods();
					goods.setTotalPrice(rs.getDouble("total_price"));
					goods.setPayTime(rs.getTimestamp("pay_time"));
					goods.setOrderNum(rs.getString("order_num"));
					goodsMap.put(goods.getOrderNum(), goods);
					goods.setTrades(new ArrayList<Trade>());	
				}
				goods.getTrades().add(trade);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.close(rs, ps, conn);
		}
		ArrayList<Goods> goodses = new ArrayList<Goods>(goodsMap.values());
		pager.setPageDate(goodses);
		return pager;
	}
	
	public ArrayList getNum(String num) { // id查找订单个数
		
		ArrayList nums=new ArrayList();
		String sql = "SELECT goods_order.order_num FROM goods_order WHERE goods_order.order_num=?";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, num);
			rs = ps.executeQuery();
			while (rs.next()) {
				
				nums.add(rs.getString("order_num"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbHelper.close(rs, ps, conn);
		}
		return nums;
	}

	public int getDataCount(Integer id) { // id查找订单个数
		int count = 0;
		String sql = "select count(*) from goods_order where goods_order.user_id=?";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}

	// 添加订单
	public void addOrder(ShopCar shopCar, UserInfo userinfo, String[] parts, String num) {
		String sql = "INSERT INTO goods_order(total_price,phone,user_id,address,income_name,order_num)VALUES(?,?,?,?,?,?)";

		ArrayList params = new ArrayList();

		params.add(shopCar.getTotalPrice());// 总价
		params.add(parts[1]);
		params.add(userinfo.getId());
		params.add(parts[0]);
		params.add(parts[2]);
		params.add(num);
		DbHelper.update(sql, params);
	}

	public Integer find(String num) // 通过订单号查询
	{
		Integer id = null;
		String sql = "SELECT * from goods_order WHERE order_num=?";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, num);
			rs = ps.executeQuery();
			while (rs.next()) {
				id = rs.getInt("id");
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.close(rs, ps, conn);
		}
		return id;
	}

	

}
